package lava.rt.persistence.sql;

import java.io.Closeable;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;

import java.util.*;
import java.util.Date;

import java.util.function.BiFunction;

import java.util.function.Supplier;

import javax.sql.CommonDataSource;
import javax.sql.DataSource;
import javax.sql.XADataSource;

import lava.rt.expcption.PersistenceExecption;
import lava.rt.persistence.DataContext;
import lava.rt.persistence.Entity;
import lava.rt.wrapper.GetSetFieldWarpper;
import lava.rt.wrapper.SqlConnectionWrapper;

@SuppressWarnings({ "unchecked", "rawtypes" })
public abstract class SqlDataContext implements DataContext, Closeable {

	

	protected static final Map<Class, Map<String, GetSetFieldWarpper>> declaredFieldMap = new HashMap<>();

	private final ThreadLocal<SqlConnectionWrapper> readConnection = new ThreadLocal<>(),
			writeConnection = new ThreadLocal<>();

	protected abstract Class<? extends SqlDataContext> thisClass();

	protected abstract Criterias getCriterias();

	protected abstract CommonDataSource getReadDataSource();

	protected abstract CommonDataSource getWriteDataSource();

	protected <M extends Entity> TableTemplate<M> createTable(String tableName, String pkName,
			Supplier<M> entityBuilder) {
		TableTemplate<M> table = null;
		table = new TableTemplate<M>(this, tableName, pkName, entityBuilder) {};
		return table;
	}

	protected <M extends Entity> ViewTemplate<M> createView(String tableName, Supplier<M> entityBuilder) {
		ViewTemplate<M> view = new ViewTemplate<M>(this, tableName, entityBuilder) {
		};
		return view;
	};

	public <M extends Entity> TableTemplate<M> getTable(Class<M> mcls) {
		TableTemplate<M> ret = null;
		try {
			for (Field field : thisClass().getDeclaredFields()) {
				if (!field.getType().equals(TableTemplate.class)) {
					continue;
				}

				field.setAccessible(true);
				TableTemplate template = (TableTemplate) field.get(this);
				if (template.entryClass.equals(mcls)
						||template.entryClass.equals(mcls.getSuperclass())
						) {
					ret = template;
					break;
				}

			}
		} catch (Exception e) {
		}
		return ret;
	}

	public <M extends Entity> ViewTemplate<M> getView(Class<M> mcls) {
		ViewTemplate<M> ret = null;
		try {
			for (Field field : thisClass().getDeclaredFields()) {
				if (!field.getType().equals(ViewTemplate.class)) {
					continue;
				}

				field.setAccessible(true);
				ViewTemplate template = (ViewTemplate) field.get(this);
				if (template.entryClass.equals(mcls)
						||template.entryClass.equals(mcls.getSuperclass())
						) {
					ret = template;
					break;
				}

			}
		} catch (Exception e) {
		}
		return ret;
	};

	

	public <E extends Entity> E getEntity(Class<E> cls, Object pk) throws PersistenceExecption {
		E ret = null;
		TableTemplate<E> table = getTable(cls);
		ret = table.loadByPk(pk);
		return ret;
	}

	public <M extends Entity> List<M> listEntities(Supplier<M> entityBuilder, SelectCommand command, int start,
			int limit) throws PersistenceExecption {
		String psql = getCriterias().toPaging(command.getSql(), start, limit);
		Object[] param = command.getParams();
		List<M> ret = listEntities(entityBuilder, psql, param);
		return ret;
	}

	public <M extends Entity> PagingResult<M> pagingEntities(Supplier<M> entityBuilder, SelectCommand command,
			int start, int limit) throws PersistenceExecption {

		List<M> rows = listEntities(entityBuilder, command, start, limit);
		PagingResult<M> ret = new PagingResult<>(rows);
		if (ret.getRows().size() == limit) {
			String countSql = command.getCountSql();
			Long total = (long) listArrays(countSql, command.getParams())[0][0];
			ret.setTotal(total.intValue());
		}
		return ret;
	}

	public <M extends Entity> void cursoringEntities(Supplier<M> entityBuilder, SelectCommand command,
			BiFunction<Integer, M, Integer> cursor) throws PersistenceExecption {

		Connection connection = getReadConnection().self;
		String sql = command.getSql();

		Object[] params = command.getParams();
		try (PreparedStatement preparedStatement = connection.prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i + 1, params[i]);
			}
			preparedStatement.setFetchSize(0);
			try (ResultSet resultSet = preparedStatement.executeQuery();) {

				ResultSetMetaData metaData = resultSet.getMetaData();
				Map<String, Integer> meteDataMap = new HashMap<String, Integer>();
				for (int i = 1; i <= metaData.getColumnCount(); i++) {
					String key = metaData.getColumnName(i).toUpperCase();
					meteDataMap.put(key, i);
				}

				int hre = 0, rowIndex = 0;
				M m = entityBuilder.get();
				Map<String, GetSetFieldWarpper> entityFieldMap = getAllDeclaredFieldMap(m.getClass());
				while (resultSet.next()) {
					rowIndex++;
					if (hre > 0) {
						hre--;
						continue;
					} else if (hre < 0) {
						break;
					}

					for (Iterator<String> it = entityFieldMap.keySet().iterator(); it.hasNext();) {
						String columnName = it.next().toUpperCase();
						Integer columnIndex = meteDataMap.get(columnName);
						if (columnIndex == null)
							continue;

						GetSetFieldWarpper field = entityFieldMap.get(columnName);
                        
						Object cellValue = resultSet.getObject(columnIndex);
						field.setter.invoke(m, cellValue);

					}

					hre = cursor.apply(rowIndex, m);
				}
			}
		} catch (Exception ex) {
			SQLException seq = new SQLException(ex);
			throw new SqlDataContextExecption(seq, sql, params);
		}

	}

	public <M extends Entity> List<M> listEntities(Supplier<M> entityBuilder, String sql, Object... params)
			throws SqlDataContextExecption {

		Objects.requireNonNull(entityBuilder);

		Connection connection = getReadConnection().self;

		List<M> list = new ArrayList<M>();
		try (PreparedStatement preparedStatement = connection.prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i + 1, params[i]);
			}

			try (ResultSet resultSet = preparedStatement.executeQuery();) {

				ResultSetMetaData metaData = resultSet.getMetaData();
				Map<String, Integer> meteDataMap = new HashMap<String, Integer>();
				for (int i = 1; i <= metaData.getColumnCount(); i++) {
					String key = metaData.getColumnName(i).toUpperCase();
					meteDataMap.put(key, i);
				}

				Map<String, GetSetFieldWarpper> entityFieldMap = null;

				while (resultSet.next()) {
					M m = entityBuilder.get();

					if (entityFieldMap == null) {
						entityFieldMap = getAllDeclaredFieldMap(m.getClass());
					}
					for (Iterator<String> it = entityFieldMap.keySet().iterator(); it.hasNext();) {
						String columnKey = it.next();
						String columnName = columnKey.toUpperCase();
						Integer columnIndex = meteDataMap.get(columnName);
						if (columnIndex == null)
							continue;

						GetSetFieldWarpper field = entityFieldMap.get(columnKey);
						Object cellValue = resultSet.getObject(columnIndex);
						field.setter.invoke(m, cellValue);

					}

					list.add(m);
				}
			}
		} catch (Exception ex) {
			SQLException seq = new SQLException(ex);
			throw new SqlDataContextExecption(seq, sql, params);
		}

		return list;
	}

	protected Map<String, GetSetFieldWarpper> getAllDeclaredFieldMap(Class<? extends Entity> cls) {

		Map<String, GetSetFieldWarpper> ret = declaredFieldMap.get(cls);
		if (ret == null) {
			ret = new HashMap();
			Map<String, Class> classMap = getSuperClassMap(cls);
			classMap.put(cls.getName(), cls);
			for (Class _cls : classMap.values()) {
				ret.putAll(getThisDeclaredFieldMap(_cls));
			}
		}
		return ret;
	}

	protected Map<String, GetSetFieldWarpper> getThisDeclaredFieldMap(Class cls) {
		Map<String, GetSetFieldWarpper> fieldMap = new HashMap<>();

		Field[] fields = cls.getDeclaredFields();
		for(Field f : fields) {
			if(fieldMap.containsKey(f.getName()) || Modifier.isStatic(f.getModifiers())
					||f.getName().equals("this$0") ) continue;
			try {
				fieldMap.put(f.getName(), new GetSetFieldWarpper(f));
			} catch (NoSuchMethodException | SecurityException e) {}
		}
		return fieldMap;
	}

	
	public Object[][] listArrays(String cmd, Object... params) throws PersistenceExecption {
		SqlConnectionWrapper connection = getReadConnection();

		Object[][] re = null;
		String sql = cmd;
		try {
			re = connection.executeQueryArray(sql, params);
		} catch (SQLException seq) {
			throw new SqlDataContextExecption(seq, sql, params);
		}
		return re;
	}

	public Object[][] listArrays(SelectCommand command, int start, int limit) throws PersistenceExecption {

		SqlConnectionWrapper connection = getReadConnection();

		Object[][] re = null;
		String sql = getCriterias().toPaging(command.getSql(), start, limit);
		Object[] params = command.getParams();
		try {
			re = connection.executeQueryArray(sql, params);
		} catch (SQLException seq) {
			throw new SqlDataContextExecption(seq, sql, params);
		}
		return re;
	}

	

	public String listJson(SelectCommand command, int start, int limit) throws PersistenceExecption {

		StringBuffer ret = new StringBuffer("[");
		int size = 0;
		String[] columns = null;
		String sql = getCriterias().toPaging(command.getSql(), start, limit);
		Object[] params = command.getParams();
		try (PreparedStatement preparedStatement = getReadConnection().self.prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i + 1, params[i]);
			}
			try (ResultSet resultSet = preparedStatement.executeQuery();) {
				ResultSetMetaData metaData = resultSet.getMetaData();
				int cc = metaData.getColumnCount();
				columns = new String[cc];
				if ("*".equals(command.columns.trim())) {
					for (int i = 0; i < cc; i++) {
						columns[i] = metaData.getColumnName(i + 1);
					}
				} else {
					String[] columnDefs = command.columns.split(",");
					for (int i = 0; i < cc; i++) {
						String columnDefi = columnDefs[i];
						String columnLabel = columnDefi;
						if (columnDefi.toLowerCase().contains("as")) {
							columnLabel = columnLabel.substring(columnLabel.lastIndexOf(" ")).trim();
						}
						columns[i] = columnLabel;
					}
				}

				while (resultSet.next()) {

					ret.append("{");

					for (int i = 0; i < cc; i++) {
						Object colObject = resultSet.getObject(i + 1);
						String colValue = null;
						if (colObject == null) {
							colValue = "null";
						} else {
							colValue = colObject.toString();
						}

						ret.append("\"").append(columns[i]).append("\"").append(":");
						if (colObject instanceof String

						) {
							ret.append("\"").append(colValue).append("\"");

						} else if (colObject instanceof java.sql.Date || colObject instanceof Date) {
							ret
									// .append("\"")
									.append(((Date) colObject).getTime())
							// .append("\"")
							;
						} else {
							ret.append(colValue);
						}
						if (i < cc - 1) {
							ret.append(",");
						}

					}

					ret.append("},");
					size++;
					// list.add(rowMap);
				}
			}
		} catch (SQLException seq) {
			throw new SqlDataContextExecption(seq, sql, params);
		}
		if (size > 0) {
			ret.deleteCharAt(ret.length() - 1);
		}
		ret
				// .append("],total:").append(total)
				.append("],size:").append(size)

		;
		return ret.toString();
	}

	public String pagingJson(SelectCommand command, int start, int limit) throws PersistenceExecption {

		Object[] param = command.getParams();

		StringBuffer ret = new StringBuffer(listJson(command, start, limit));

		long size = Integer.parseInt(ret.substring(ret.lastIndexOf("size:") + 5)), total = start + size;
		if (size == limit) {
			// String csql=pagingParam.countSql();
			String countSql = command.getCountSql();
			total = (long) listArrays(countSql, param)[0][0];
		}
		ret.append(",total:").append(total);
		return ret.toString();
	}

	public int executeUpdate(String sql, Object... param) throws SqlDataContextExecption {
		int re = 0;

		SqlConnectionWrapper connection = getWriteConnection();

		try {
			re += connection.executeUpdate(sql, param);
		} catch (SQLException seq) {

			throw new SqlDataContextExecption(seq, sql, param);
		}

		return re;
	}

	public int executeBatch(String sql, Object[]... params) throws SqlDataContextExecption {
		int re = 0;

		SqlConnectionWrapper connection = getWriteConnection();
		try {
			re += connection.executeBatch(sql, params);
		} catch (SQLException seq) {
			throw new SqlDataContextExecption(seq, sql, params[0]);
		}

		return re;
	}

	public int addEntity(Entity entity) throws PersistenceExecption {
		int re = 0;
		Class<? extends Entity> cls = entity.getClass();

		TableTemplate table = this.getTable(cls);

		re += table.insert(entity);

		return re;
	}

	public <E extends Entity> int addEntities(Collection<E> entites) throws PersistenceExecption {
		int re = 0;
		Class cls = entites.iterator().next().getClass();
		TableTemplate table = this.getTable(cls);

		re += table.insertBatch(entites);

		return re;
	}

	public int setEntity(Entity entity) throws PersistenceExecption {
		int re = 0;

		Class cls = entity.getClass();
		TableTemplate table = this.getTable(cls);
		re += table.update(entity);
		return re;
	}

	public int setEntities(SelectCommand command, String set) throws PersistenceExecption {

		int re = 0;
		String sql = command.getUpdateSql(set);
		Object[] params = command.getParams();
		try {
			re = executeUpdate(sql, params);
		} catch (PersistenceExecption seq) {
			throw new SqlDataContextExecption(seq, sql, params);
		}
		return re;
	}

	public <E extends Entity> int setEntities(Collection<E> entites) throws PersistenceExecption {
		int re = 0;
		Class cls = entites.iterator().next().getClass();
		TableTemplate table = this.getTable(cls);

		re += table.updateBatch(entites);

		return re;
	}

	public int removeEntity(Entity entity) throws PersistenceExecption {
		int re = 0;

		Class cls = entity.getClass();
		TableTemplate table = this.getTable(cls);

		re += table.deleteByPk(entity.thisPk());

		return re;
	}

	public int removeEntities(SelectCommand command) throws PersistenceExecption {

		int re = 0;
		String sql = command.getDeleteSql();
		Object[] params = command.getParams();
		try {
			re = executeUpdate(sql, params);
		} catch (PersistenceExecption seq) {
			throw new SqlDataContextExecption(seq, sql, params);
		}
		return re;
	}

	public <E extends Entity> int removeEntities(Collection<E> entites) throws PersistenceExecption {
		int re = 0;

		Class cls = entites.iterator().next().getClass();
		TableTemplate table = this.getTable(cls);
		Object[] pks = entites.stream().map(e -> {
			return e.thisPk();
		}).toArray();

		re += table.deleteBatchByPk(pks);

		return re;
	}

	public void executeSetAutoCommit(boolean b) throws PersistenceExecption {
		try {
			Connection conn = getWriteConnection().self;
			conn.setAutoCommit(b);

		} catch (SQLException sex) {
			throw new SqlDataContextExecption(sex, "setAutoCommit", b);
		}

	}

	public void executeCommit() throws PersistenceExecption {
		try {
			Connection conn = getWriteConnection().self;
			conn.commit();

		} catch (SQLException sex) {
			throw new SqlDataContextExecption(sex, "executeCommit");
		}
	}

	public void executeRollback(Savepoint point) throws PersistenceExecption {
		Connection connection = getWriteConnection().self;
		try {
			connection.rollback(point);

		} catch (SQLException sex) {
			throw new SqlDataContextExecption(sex, "executeRollback");
		}
	}

	public Savepoint executeSetSavepoint(String pointName) throws PersistenceExecption {
		Connection connection = getWriteConnection().self;
		Savepoint ret0 = null;
		try {

			ret0 = connection.setSavepoint(pointName);
		} catch (SQLException e) {
			throw new SqlDataContextExecption(e, "executeSetSavepoint", pointName);
		}

		return ret0;
	}

	private SqlConnectionWrapper getConnection(CommonDataSource ds) throws SQLException {
		Connection conn = null;
		if (ds instanceof DataSource) {
			conn = ((DataSource) ds).getConnection();
		} else if (ds instanceof XADataSource) {
			conn = ((XADataSource) ds).getXAConnection().getConnection();
		}
		SqlConnectionWrapper ret = new SqlConnectionWrapper(conn);
		return ret;
	}

	@SuppressWarnings("resource")
	protected final SqlConnectionWrapper getReadConnection() throws SqlDataContextExecption {

		SqlConnectionWrapper ret = readConnection.get();
		if (ret == null) {
			try {
				ret = getConnection(getReadDataSource());
				readConnection.set(ret);
			} catch (SQLException e) {
				throw new SqlDataContextExecption(e, "getReadConnection");
			}
		}
		return ret;
	}

	@SuppressWarnings("resource")
	protected final SqlConnectionWrapper getWriteConnection() throws SqlDataContextExecption {

		SqlConnectionWrapper ret = writeConnection.get();
		if (ret == null) {
			try {
				CommonDataSource wds = getWriteDataSource(), rds = getReadDataSource();
				if (wds.equals(rds)) {
					ret = getReadConnection();
				} else {
					ret = getConnection(wds);
				}
				writeConnection.set(ret);
			} catch (SQLException e) {
				throw new SqlDataContextExecption(e, "getWriteConnection");
			}
		}

		return ret;
	}

	protected Object[][] callProcedure(String procName, Object... params) throws PersistenceExecption {

		List<Object[]> ret = new ArrayList<Object[]>();
		StringBuffer sql = new StringBuffer();
		sql.append("{call ").append(procName).append("(");

		String[] paramStrs = new String[params.length];
		for (int i = 0; i < paramStrs.length; i++) {

			paramStrs[i] = "?";

		}
		sql.append(String.join(",", paramStrs));

		sql.append(")}");
		int cc = 0;
		try (CallableStatement call = getWriteConnection().self.prepareCall(sql.toString());) {
			boolean isOutputParam = false;
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof OutputParam) {
					OutputParam outputParam = (OutputParam) params[i];
					call.registerOutParameter(i + 1, outputParam.sqlType);
					if (outputParam.value != null) {
						call.setObject(i + 1, outputParam.value);
					}
					isOutputParam = true;
				} else {
					call.setObject(i + 1, params[i]);
				}

			}

			call.execute();

			ResultSet resultSet = call.getResultSet();
			ResultSetMetaData metaData = resultSet.getMetaData();
			cc = metaData.getColumnCount();
			while (resultSet.next()) {
				Object[] objects = new Object[cc];
				for (int i = 0; i < cc; i++) {
					objects[i] = resultSet.getObject(i + 1);
				}
				ret.add(objects);
			}

			if (isOutputParam) {
				call.getMoreResults();
				Object[] outRe = new Object[params.length];
				for (int i = 0; i < params.length; i++) {
					if (params[i] instanceof OutputParam) {
						OutputParam outputParam = (OutputParam) params[i];
						outputParam.result = call.getObject(i + 1);
						outRe[i] = outputParam.result;
					}

				}
				ret.add(outRe);
			}

		} catch (SQLException sex) {
			throw new SqlDataContextExecption(sex, procName, params);
		}
		return ret.toArray(new Object[ret.size()][cc]);

	}

	private Map<String, Class> getSuperClassMap(Class mcls) {
		Map<String, Class> re = new HashMap<String, Class>();
		for (Class cl = mcls.getSuperclass(); !Object.class.equals(cl); cl = cl.getSuperclass()) {
			re.put(cl.getName(), cl);
		}
		return re;
	}

	@Override
	public void close() {
		// TODO Auto-generated method stub

		try {
			getWriteConnection().self.close();
			getReadConnection().self.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		;
	}

}
